const Service = require('../../../core/service/ApiService');

class DataDao extends Service {
    //数据分析服务对象统计
    async serviceObj(community, subdistrict) {
        if (!community) {
            return this.model.Aged.findAndCountAll({
                where: {
                    status: this.constant.AGED_STATUS.ENABLE
                },
                attributes: ['aged_id', 'sex'],
                raw: true
            })
        } else if (community && !subdistrict) {
            return this.model.Aged.findAndCountAll({
                where: {
                    community_id: community,
                    status: this.constant.AGED_STATUS.ENABLE
                },
                attributes: ['aged_id', 'sex'],
                raw: true
            })
        } else {
            return this.model.Aged.findAndCountAll({
                where: {
                    community_id: community,
                    subdistrict_id: subdistrict,
                    status: this.constant.AGED_STATUS.ENABLE
                },
                attributes: ['aged_id', 'sex'],
                raw: true
            })
        }
    }

    //年龄组
    async getAgeGroup(community, subdistrict) {
        if (!community) {
            const sql = `select
                         sum(case when age between 60 and 65 then 1 else 0 end) as '60-65',
                         sum(case when age between 65 and 70 then 1 else 0 end) as '65-70',
                         sum(case when age between 70 and 75 then 1 else 0 end) as '70-75',
                         sum(case when age between 75 and 80 then 1 else 0 end) as '75-80',
                         sum(case when age between 80 and 85 then 1 else 0 end) as '80-85',
                         sum(case when age between 85 and 90 then 1 else 0 end) as '85-90',
                         sum(case when age >90 then 1 else 0 end) as '90'
                         from (SELECT ROUND(DATEDIFF(CURDATE(),CAST(birth as date))/365.2422) as age from aged 
                         where status = ${this.constant.AGED_STATUS.ENABLE}) as t`;
            const result = await this.model.query(sql, {type: this.model.QueryTypes.SELECT});
            return result.length === 0 ? null : result[0];
        } else if (community && !subdistrict) {
            const sql = `select
                         sum(case when age between 60 and 65 then 1 else 0 end) as '60-65',
                         sum(case when age between 65 and 70 then 1 else 0 end) as '65-70',
                         sum(case when age between 70 and 75 then 1 else 0 end) as '70-75',
                         sum(case when age between 75 and 80 then 1 else 0 end) as '75-80',
                         sum(case when age between 80 and 85 then 1 else 0 end) as '80-85',
                         sum(case when age between 85 and 90 then 1 else 0 end) as '85-90',
                         sum(case when age >90 then 1 else 0 end) as '90'
                         from (SELECT ROUND(DATEDIFF(CURDATE(),CAST(birth as date))/365.2422) as age from aged 
                         where status = ${this.constant.AGED_STATUS.ENABLE} and community_id = ${community}) as t`;
            const result = await this.model.query(sql, {type: this.model.QueryTypes.SELECT});
            return result.length === 0 ? null : result[0];
        } else {
            const sql = `select
                         sum(case when age between 60 and 65 then 1 else 0 end) as '60-65',
                         sum(case when age between 65 and 70 then 1 else 0 end) as '65-70',
                         sum(case when age between 70 and 75 then 1 else 0 end) as '70-75',
                         sum(case when age between 75 and 80 then 1 else 0 end) as '75-80',
                         sum(case when age between 80 and 85 then 1 else 0 end) as '80-85',
                         sum(case when age between 85 and 90 then 1 else 0 end) as '85-90',
                         sum(case when age >90 then 1 else 0 end) as '90'
                         from (SELECT ROUND(DATEDIFF(CURDATE(),CAST(birth as date))/365.2422) as age from aged 
                         where status = ${this.constant.AGED_STATUS.ENABLE} and community_id = ${community} and subdistrict_id = ${subdistrict}) as t`;
            const result = await this.model.query(sql, {type: this.model.QueryTypes.SELECT});
            return result.length === 0 ? null : result[0];
        }
    }

    //获取服务人员列表
    async getStaffList(town_id) {
        return this.model.ServiceStaff.findAll({
            where: {
                town_id: town_id,
                status: this.constant.STAFF_STATUS.ENABLE
            },
            attributes: ['svc_staff_id', 'name'],
            raw: true
        })
    }

    //服务项目分析
    async getServiceAmount(begin, end, svc_staff_id, aged_id) {
        if (!svc_staff_id && !aged_id) {
            const sql = `select count(*) as svc_cnt,date_format(service_record.start_time,'%m-%d') as date
                    from service_record
                    where service_record.start_time between '${begin}' and '${end}'
                    and service_record.status not in (${this.constant.SVC_STATUS.EXCEPTION})
                    group by date
                    order by date asc`;
            return this.model.query(sql, {type: this.model.QueryTypes.SELECT});
        } else if (svc_staff_id) {
            const sql = `select count(*),date_format(service_record.start_time,'%m-%d') as date
                    from service_record
                    where service_record.start_time between '${begin}' and '${end}'
                    and svc_staff_id = '${svc_staff_id}' and service_record.status not in (${this.constant.SVC_STATUS.EXCEPTION})
                    group by date
                    order by date asc`;
            return this.model.query(sql, {type: this.model.QueryTypes.SELECT});
        } else {
            const sql = `select count(*),date_format(service_record.start_time,'%m-%d') as date
                    from service_record
                    where service_record.start_time between '${begin}' and '${end}'
                    and aged_id = '${aged_id}' and service_record.status not in (${this.constant.SVC_STATUS.EXCEPTION})
                    group by date
                    order by date asc`;
            return this.model.query(sql, {type: this.model.QueryTypes.SELECT});
        }
    }

    //评分
    async getScoreRate(begin, end, svc_staff_id, aged_id) {

        if (!svc_staff_id && !aged_id) {
            const sql = `
                    select score,count(score) as cnt
                    from service_record
                    where end_time between '${begin}' and '${end}'
                    group by score
                    order by score asc`;
            return this.model.query(sql, {type: this.model.QueryTypes.SELECT});
        } else if (svc_staff_id) {
            const sql = `
                    select end_time,score,count(score) as cnt
                    from service_record
                    where svc_staff_id = '${svc_staff_id}'
                    and end_time between '${begin}' and '${end}'
                    group by score
                    order by score asc`;
            return this.model.query(sql, {type: this.model.QueryTypes.SELECT});
        } else if (aged_id) {
            const sql = `
                    select end_time,score,count(score) as cnt
                    from service_record
                    where aged_id = '${aged_id}'
                    and end_time between '${begin}' and '${end}'
                    group by score
                    order by score asc`;
            return this.model.query(sql, {type: this.model.QueryTypes.SELECT});
        }
    }

    async getSvcItemListOfSvcRecord(begin, end, svc_staff_id, aged_id) {
        const opt = {
            where: {
                start_time: {
                    $between: [begin, end]
                },
                status: this.constant.SVC_STATUS.COMPLETED
            },
            attributes: ['svc_item_list'],
            raw: true
        };
        if (svc_staff_id) {
            opt.where.svc_staff_id = svc_staff_id;
        }
        if (aged_id) {
            opt.where.aged_id = aged_id;
        }
        return this.model.ServiceRecord.findAll(opt)
    }

    async getSvcItemMetaList() {
        return this.model.ServiceItem.findAll({
            // where: {
            //     status: this.constant.SVC_ITEM_STATUS.ENABLE
            // },
            attributes: ['svc_item_id', 'name'],
            raw: true
        })
    }

}

module.exports = DataDao;
